package com.data.king.tenda.service;

import android.nfc.Tag;
import android.util.Log;

import com.data.king.tenda.bean.Equip;
import com.data.king.tenda.bean.TendaData;

import java.sql.Connection;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by lenovo on 2018/1/9.
 */

public class DBHelper {
    private static final String REMOTE_IP = "bdm256727264.my3w.com";//"59.110.55.120";//"bdm256727264.my3w.com";
    private static final String URL = "jdbc:mysql://" + REMOTE_IP + "/bdm256727264_db";
    private static final String USER = "bdm256727264";
    private static final String PASSWORD = "1235789zlq";
    private static final String TAG = "DBHelper";

    private static Connection conn;

    static {
        if(conn == null){
            conn = Util.openConnection(URL, USER, PASSWORD);
        }
        Log.d(TAG,"conn是否为null " + (conn == null));
    }

    private DBHelper(){
    }

    public static void destroy(){
        Log.d(TAG, "onDestroy() executed");
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                conn = null;
            } finally {
                conn = null;
            }
        }
    }

    public static void Insert(TendaData data){
        if (data == null) return;
        StringBuilder sql = new StringBuilder("INSERT INTO `bdm256727264_db`.`tenda` (`qosListHostname`, `qosListRemark`, `qosListIP`, `qosListConnectType`, `qosListMac`, `qosListDownSpeed`, `qosListUpSpeed`, `qosListDownLimit`, `qosListUpLimit`, `qosListAccess`, `datetime`) VALUES ");
        List<Equip> list = data.getEquips();
        SimpleDateFormat mSimpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String nowDateTime = mSimpleDateFormat.format(new Date());
        if(list != null && list.size() > 0){
            int size = list.size();
            for (int i = 0; i < size; i++) {
                sql.append("('").append(list.get(i).getQosListHostname())
                        .append("','").append(list.get(i).getQosListRemark())
                        .append("','").append(list.get(i).getQosListIP())
                        .append("','").append(list.get(i).getQosListConnectType())
                        .append("','").append(list.get(i).getQosListMac())
                        .append("','").append(list.get(i).getQosListDownSpeed())
                        .append("','").append(list.get(i).getQosListUpSpeed())
                        .append("','").append(list.get(i).getQosListDownLimit())
                        .append("','").append(list.get(i).getQosListUpLimit())
                        .append("','").append(list.get(i).getQosListAccess())
                        .append("','").append(nowDateTime).append("')");
                if (i != (size-1))
                    sql.append(",");
                else
                    sql.append(";");
            }
            Log.d(TAG,"执行SQL：" + sql);
            if(conn == null){
                conn = Util.openConnection(URL, USER, PASSWORD);
            }
            Log.d(TAG,"conn是否为null " + (conn == null));
            boolean f =Util.execSQL(conn, sql.toString());
            DBHelper.destroy();
            //Log.d(TAG,"数据存储结果：" + f);
        }
    }


    public static void onInsert() {
        String sql = "INSERT INTO `bdm256727264_db`.`tenda` (`qosListHostname`, `qosListRemark`, `qosListIP`, `qosListConnectType`, `qosListMac`, `qosListDownSpeed`, `qosListUpSpeed`, `qosListDownLimit`, `qosListUpLimit`, `qosListAccess`, `datetime`) VALUES ('king2', 'remark', '127.0.0.1', '111', '11', '123', '1234', '123', '345', '23', '2018-01-08 13:50:54');";
        boolean f =Util.execSQL(conn, sql);
        //Log.d(TAG,"执行INSERT完毕：" + f);
    }

    public static void onDelete() {
        String sql = "delete from mytable where name='mark'";
        Util.execSQL(conn, sql);
    }

    public static void onUpdate() {
        String sql = "update mytable set name='lilei' where name='hanmeimei'";
        Util.execSQL(conn, sql);
    }

    public static void onQuery() {
        Util.query(conn, "select * from mytable");
    }
}
